/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import dto.Account;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import utils.DBConnection;

/**
 *
 * @author lab
 */
public class AccountDAO {

    Connection conn = null;
    String sqlSelectAllAccount = "select * from Account";
    String sqlReadByUserPass = "select * from Account where Username=? and Password=?";
    String sqlReadByUser = "select * from Account where Username=?";
    String sqlInsert = "insert into Account(username,password,rolename, perID) values(?, ?, ?,?)";
    String sqlUpdate = "update Account set username=?,password=?,rolename=?,perID=? where id=?";
    String sqlDelete = "delete from Account where username=?";

    public AccountDAO() {
    }

    /**
     * Get All Accounts
     * @return All Accounts
     */
    public ArrayList<Account> getAllAccounts() {
        ArrayList<Account> list = new ArrayList<Account>();
        try {
            conn = DBConnection.getConnection();
            if (conn == null) {
                return list;
            }
            PreparedStatement ps = conn.prepareStatement(sqlSelectAllAccount);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Account acc = new Account();
                acc.setAccountID(rs.getInt(1));
                acc.setUsername(rs.getString(2));
                acc.setPassword(rs.getString(3));
                acc.setRolename(rs.getString(4));
                //acc.getPer().setPerID(rs.getInt(5));

                list.add(acc);
            }

            DBConnection.disconnect();
        } catch (SQLException ex) {
            Logger.getLogger(AccountDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return list;
    }

    public Account getAccountToCheckLogin(String username, String password) {
        Account Account = new Account();
        try {

            conn = DBConnection.getConnection();
            if (conn == null) {
                return Account;
            }
            PreparedStatement ps = conn.prepareStatement(sqlReadByUserPass);
            ps.setString(1, username);
            ps.setString(2, password);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                Account.setUsername(rs.getString("Username"));
                Account.setPassword(rs.getString("Password"));
                DBConnection.disconnect();
            }

        } catch (SQLException ex) {
            Logger.getLogger(AccountDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return Account;
    }

    public boolean insertAccouny(Account acc) {
        int effectedRows = 0;
        try {

            conn = DBConnection.getConnection();
            PreparedStatement ps = conn.prepareStatement(sqlInsert);
            ps.setString(1, acc.getUsername());
            ps.setString(2, acc.getPassword());
            ps.setString(3, acc.getRolename());
            ps.setInt(4, acc.getPer().getPerID());
            effectedRows = ps.executeUpdate();
            DBConnection.disconnect();
        } catch (SQLException ex) {
            Logger.getLogger(AccountDAO.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
        if (effectedRows > 0) {
            return true;
        }
        return false;

    }

    public Account getAccByID(String username) {
        Account acc = new Account();
        try {

            conn = DBConnection.getConnection();
            if (conn == null) {
                return acc;
            }
            PreparedStatement ps = conn.prepareStatement(sqlReadByUser);
            ps.setString(1, username);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                acc.setUsername(rs.getString("username"));
                acc.setPassword(rs.getString("password"));
                acc.setRolename(rs.getString("role"));

                DBConnection.disconnect();
            }

        } catch (SQLException ex) {
            Logger.getLogger(AccountDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return acc;
    }

    public boolean update(Account user) {
        int effectedRows = 0;
        try {

            conn = DBConnection.getConnection();
            PreparedStatement ps = conn.prepareStatement(sqlUpdate);
            ps.setString(1, user.getUsername());
            ps.setString(2, user.getPassword());
            ps.setString(3, user.getRolename());
            ps.setInt(4, user.getPer().getPerID());

            ps.setInt(5, user.getAccountID());
            effectedRows = ps.executeUpdate();
            DBConnection.disconnect();
        } catch (SQLException ex) {
            Logger.getLogger(AccountDAO.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
        if (effectedRows > 0) {
            return true;
        }
        return false;

    }

    public boolean delete(String username) {
        int effectedRows = 0;
        try {
            conn = DBConnection.getConnection();
            PreparedStatement ps = conn.prepareStatement(sqlDelete);
            ps.setString(1, username);
            effectedRows = ps.executeUpdate();
            DBConnection.disconnect();
        } catch (SQLException ex) {
            Logger.getLogger(AccountDAO.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
        if (effectedRows > 0) {
            return true;
        }
        return false;

    }
}
